package com.orange;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.cncitrus.data.common.Util;

import orange.database.DBIns;

public class OzYieldandAreaconn extends BaseAction{
	
	public OzYieldandAreaconn(String flag, String[] str1, String[] str2) throws SQLException {
		
		String sql;
		ResultSet rs;
		Connection conn ;
		switch (Integer.parseInt(flag)) {
		case 1:// 产量
			sql = change1_1_1(str1, str2);
			System.out.println(sql);
			conn = DBIns.getInstance().getConnection();
			rs = conn.createStatement().executeQuery(sql);
//			rs = excutequery(sql);
			value = combine1_1_1(rs);
			DBIns.getInstance().release(conn);
			break;
		case 2:// 面积
			sql = change1_1_2(str1, str2);
			System.out.println(sql);
			conn = DBIns.getInstance().getConnection();
			rs = conn.createStatement().executeQuery(sql);
			value = combine1_1_2(rs);
			DBIns.getInstance().release(conn);
			break;

		}

	}

	public String change1_1_1(String[] str1, String[] str2) {
		try {
			int n = str1.length;
			nianfen = Util.Convert(str1);

			int m = str2.length;
			chandi = Util.Convert(str2);

			String nnianfen = new String(str1[n - 1].getBytes("ISO-8859-1"),
					"UTF-8");
			// String cchandi= new
			// String(str2[m-1].getBytes("ISO-8859-1"),"GBK");
			String cchandi = new String(str2[m - 1].getBytes("ISO-8859-1"),
					"utf-8");
			String sql = "1";
			if (nnianfen.equals("所有年份")) {
				if (cchandi.equals("所有省份"))
					sql = "select * from orange_z_chanliang order by NianFen";
				else
					sql = "select * from orange_z_chanliang where ShengFen in ("
							+ chandi + ") order by NianFen";
			} else {
				if (cchandi.equals("所有省份"))
					sql = "select * from orange_z_chanliang where NianFen in ("
							+ nianfen + ") order by NianFen";
				else
					sql = "select * from orange_z_chanliang where ShengFen in ("
							+ chandi
							+ ") and NianFen in ("
							+ nianfen
							+ ") order by NianFen";
			}
			return sql;
		} catch (Exception e) {
			return "error";
		}
	}

	public String change1_1_2(String[] str1, String[] str2) {

		try {
			int n = str1.length;
			nianfen = Util.Convert(str1);

			int m = str2.length;
			chandi = Util.Convert(str2);

			String nnianfen = new String(str1[n - 1].getBytes("ISO-8859-1"),
					"UTF-8");
			// String cchandi = new String(str2[m - 1].getBytes("ISO-8859-1"),
			// "GBK");
			String cchandi = new String(str2[m - 1].getBytes("ISO-8859-1"),
					"UTF-8");

			String sql = "1";
			if (nnianfen.equals("所有年份")) {
				if (cchandi.equals("所有省份"))
					sql = "select * from orange_z_mianji order by Nianfen";
				else
					sql = "select * from orange_z_mianji where ShengFen in("
							+ chandi + ") order by Nianfen";
			} else {
				if (cchandi.equals("所有省份"))
					sql = "select * from orange_z_mianji where NianFen in ("
							+ nianfen + ") order by Nianfen";
				else
					sql = "select * from orange_z_mianji where ShengFen in ("
							+ chandi + ") and NianFen in (" + nianfen
							+ ") order by Nianfen";
			}
			return sql;
		} catch (Exception e) {
			return "error";
		}
	}

	public String combine1_1_1(ResultSet rs1) {
		try {
			rs1.beforeFirst();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		StringBuffer buffer1 = new StringBuffer();
		buffer1
				.append("<thead><tr><td>年份</td><td>省份</td><td>柑橘类(吨)</td><td>柑(吨)</td><td>桔(吨)</td><td>橙(吨)</td><td>柚(吨)</td></tr></thead>");
		try {
			while (rs1.next()) {
				buffer1.append("<tr><td>" + rs1.getString("NianFen")
						+ "</td><td>" + rs1.getString("ShengFen") + "</td><td>"
						+ rs1.getString("GanJuLei") + "</td><td>"
						+ rs1.getString("Gan") + "</td><td>"
						+ rs1.getString("Ju") + "</td><td>"
						+ rs1.getString("Cheng") + "</td><td>"
						+ rs1.getString("You") + "</td></tr>");
			}
			value = new String(buffer1);
			return value;
		} catch (Exception e) {
			e.printStackTrace();
			return "-1";
		}
	}

	public String combine1_1_2(ResultSet rs1) {
		try {
			rs1.beforeFirst();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		StringBuffer buffer1 = new StringBuffer();
		buffer1
				.append("<thead><tr><td>年份</div></td><td>省份</td><td>种植面积(千公顷)</td></tr></thead>");
		try {
			while (rs1.next()) {
				buffer1.append("<tr><td>" + rs1.getObject("NianFen")
						+ "</td><td>" + rs1.getObject("ShengFen") + "</td><td>"
						+ rs1.getObject("GanJu") + "</td></tr>");
			}
			value = new String(buffer1);
			return value;
		} catch (Exception e) {
			return "-1";
		}
	}
	public String getValue() {
		return value;
	}
	public static void main(String[] str) {
		try {
			String[] str1 = { "2005", "2006" }, str2 = { "所有省份" };

			OzYieldandAreaconn oc = new OzYieldandAreaconn("1", str1, str2);

			OzYieldandAreaconn oc_1 = new OzYieldandAreaconn("2", str1, str2);
			System.out.println(oc.getValue());
			System.out.println(oc_1.getValue());
		} catch (Exception e) {
			// TODO: handle exception
		}
	}
}
